package dbLayer;

import modelLayer.Teacher;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 * This class is a database layer class of the MSAS system. It manipulates database
 * information of teacher objects. This class implements its interface, and have therefore
 * no direct connection to the control layer.
 * 
 * @author Danny M�lgaard, Anna Kelemen, Jakob Korsgaard
 * 		   
 * @version 1.3.1
 * 					1.3.1: 		Added more extensive documentation. Verified that
 * 								information contained in the Design Class Diagram,
 * 								is in accordance with the information contained in this
 * 								class.
 */
public class DBTeacher implements DBTeacherIF {
	
	private Connection dbConnection;
	
	public DBTeacher() {
		
		this.dbConnection = DBConnection.getInstance().getDBcon();
		
	}
	
	@Override
	public int insert(Teacher t) throws SQLException {
		
		int rc = -1;
		String query = "INSERT INTO Teacher (name, cpr, address, phone, email, hours) "
					   + "values(?, ?, ?, ?, ?, ?)";		
		try {
			PreparedStatement ps = dbConnection.prepareStatement(query);
			ps.setString(1, t.getName());
			ps.setString(2, t.getCpr());
			ps.setString(3, t.getAddress());
			ps.setString(4, t.getPhone());
			ps.setString(5, t.getEmail());
			ps.setInt(6, t.getHours());
			rc = ps.executeUpdate();
		} 		 
		catch (SQLException e) {
			System.out.println("Insertion of teacher in the dabase failed. Reason: " + e.getMessage());
			throw new SQLException();
		}	   
		
		return rc;

	}
	
	@Override
	public List<Teacher> find(String name) throws Exception {
	
		String wClause = "name like '%" + name + "%'"; 
		return miscWhere(wClause);
		
	}
	
	@Override
	public Teacher findById(int tId) {
		
		String wClause = "id = '" + tId + "'"; 
		return singleWhere(wClause);
		
	}
	
	@Override
	public List<Teacher> getAll(){
		
		return miscWhere("");
		
	}
	
	@Override
	public int update(int id, String name, String cpr, String address, String phone,
					  String email, int hours) throws SQLException {
		
		int rc = -1;
		String query = "UPDATE Teacher SET name = ?, cpr = ?, address = ?, phone = ?, email = ?, hours = ? where id = ? "; 
					   
		try {
			PreparedStatement ps = dbConnection.prepareStatement(query);
			ps.setString(1, name);
			ps.setString(2, cpr);
			ps.setString(3, address);
			ps.setString(4, phone);
			ps.setString(5, email);
			ps.setInt(6, hours);
			ps.setInt(7, id);
			rc = ps.executeUpdate();
		} 		 
		catch (SQLException e) {
			System.out.println("Updating of teacher in the dabase failed. Reason: " + e.getMessage());
			throw new SQLException();
		}	
		
		return rc;
		
	}
	
	@Override
	public int remove(int id) throws Exception {
		
		int rc = -1;
		String query = "DELETE FROM Teacher WHERE id = " + id;
		System.out.println(id + " :" + query);
		try {
			Statement stmt = dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch (Exception e) {
			System.out.println("Deletion of teacher in the dabase failed. Reason: " + e.getMessage());
		}
		System.out.println(rc);
		
		return rc;
		
	}
	
	/**
	 * Builds a query with a where clause (condition), and returns a single object
	 * to the caller. This method is used when only wanting one object built from 
	 * the database. This method takes the following parameters:
	 *  
	 * @param 	wClause				String		the search condition, and where clause of the SQL query.
	 * @param 	retriveAssociation	boolean		states if we're to retrieve associations in the object aswell.
	 * @return	t					Teacher		a built Teacher object, based on query results from the database.
	 */
	private Teacher singleWhere(String wClause) {
		
		ResultSet rs;
		Teacher t = null;
		String query = buildQuery(wClause);
		
		try {
			Statement stmt = dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			rs = stmt.executeQuery(query);
			if(rs.next()) {
				t = buildObject(rs);
			}
			stmt.close();	
		}
		catch(Exception e) {
			System.out.println("DEBUG MESSAGE [TEACHER]: singleWhere(): " + e);
		}
		
		return t;
		
	}
	
	/**
	 * Builds a query with a where clause (condition), and returns a collection of objects
	 * to the caller. This method is used when only wanting one or more objects built from 
	 * the database. This method takes the following parameters:
	 *  
	 * @param 	wClause				String				the search condition, and where clause of the SQL query.
	 * @param 	retriveAssociation	boolean				states if we're to retrieve associations in the object aswell.
	 * @return	list				List<Teacher>		a list of built Teacher objects, based on query results from the 
	 * 													database.
	 */
	private List<Teacher> miscWhere(String wClause) {
		
		ResultSet rs;
		String query = buildQuery(wClause);
		List<Teacher> list = new ArrayList<Teacher>();
		
		try {
			Statement stmt = this.dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				Teacher t = buildObject(rs);
				list.add(t);	
			}
			stmt.close();
		}
 		catch(Exception e) {
	 		System.out.println("DEBUG MESSAGE [TEACHER]: miscWhere(): " + e);
 		}
		
		return list;
		
	}	
	
	/**
	 * Builds a query with a where clause (condition), and returns a string to the caller.
	 * This method takes the following parameters:
	 *  
	 * @param 	wClause				String		the search condition, and where clause of the SQL query.
	 * @return	query				String		an SQL query as a String.
	 */
	private String buildQuery(String wClause) {
		
		String query = "SELECT id, name, cpr, address, phone, email, hours FROM Teacher";
		if (wClause.length()>0) {
			query = query + " where " + wClause;
		}
		
		return query;
		
	}	
	
	/**
	 * builds an object of type Teacher based on the resultset of the SQL query. Returns this
	 * object to the caller.
	 * 
	 * @param 	rs		ResultSet		the resultset from the SQL query.
	 * @return	t		Teacher			the build Teacher object.
	 */
	private Teacher buildObject(ResultSet rs) {
		
		Teacher t = null;
		try {
			Statement stmt = this.dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			t = new Teacher(rs.getString("name"),
							rs.getString("cpr"),
							rs.getString("address"),
							rs.getString("phone"),
							rs.getString("email"),
							rs.getInt("hours"));
			t.setId(Integer.parseInt(rs.getString("id")));
		}
		catch(Exception e) {
            System.out.println("DEBUG MESSAGE [TEACHER]: buildObject(): " + e);
		}
		
		return t;
		
	}

}

